package util;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;

import org.alex.samples.model.User;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
	public	static final String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";
	public	static final String OFFICE_EXCEL_2003_POSTFIX = ".xls";
	public static Workbook openWorkbook(InputStream is, String postfix) {
		Workbook workbook = null;
		try{
			if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
				workbook = new HSSFWorkbook(is);
			}else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
				workbook = new XSSFWorkbook(is);
			}
		}catch (Exception e) {
			if(e instanceof NotOLE2FileException){
				return null;
			}
			e.printStackTrace();
		}
		return workbook;
	}
	public static List<User> getStudentListByExcel(Workbook wk) throws ParseException {
		final String GENDER_MALE_STRING = "男",GENDER_FEMALE_STRING = "女";
		final Integer GENDER_MALE_INTEGER = 1,GERNDER_FEMALE_INTEGER = 0;
		List<User> returnList = new ArrayList<User>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNumber = 0;
		int maxRowNumber = sheet.getLastRowNum();
		User user = null;
		for(int currentRowNumber = 1;currentRowNumber<= maxRowNumber;currentRowNumber++){
			Row row = sheet.getRow(currentRowNumber);
			maxCellNumber = row.getLastCellNum();
			user= new User();
			for(int currentCellNumber = 0;currentCellNumber<maxCellNumber;currentCellNumber++){
				Cell cell = row.getCell(currentCellNumber);
				switch (currentCellNumber) {
				case 0:
					user.setId(new Integer(getValue(cell)));
					break;
				case 1:
					user.setName((String)getValue(cell));
					break;
				case 2:
					String gender = (String)getValue(cell);
					if(GENDER_MALE_STRING.equals(gender)){
						user.setGender(GENDER_MALE_INTEGER);
					} else if(GENDER_FEMALE_STRING.equals(gender)){
						user.setGender(GERNDER_FEMALE_INTEGER);
					}
					break;
				case 3:
					user.setTel((String)getValue(cell));
					break;
				case 4:
					user.setEmail((String)getValue(cell));
					break;
				case 5:
					user.setAddress((String)getValue(cell));
					break;
				case 6:
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					Date birhdaydate = sdf.parse((String)getValue(cell));
					user.setBirthday((Date)birhdaydate);
					break;
				case 7:
					String decodingPassword = (String)getValue(cell);
					user.setPassword(MD5Utils.getMD5Code(decodingPassword));
					break;
				default:
					break;
				}
			}
			user.setLogin_fail_count(0);
			Calendar now = Calendar.getInstance();
			user.setLast_lock_time(now.getTime());
			user.setExamUserSubject(new HashSet<>());
			returnList.add(user);
		}
		return returnList;
	}
	@SuppressWarnings({ "static-access" })
	private static String getValue(Cell cell){
		if (cell == null) {
			return "";
		}
		if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
			// 处理日期格式
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
			}
			DecimalFormat df = new DecimalFormat("0");

			return String.valueOf(df.format(cell.getNumericCellValue()));
		} else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) {
			return String.valueOf(cell.getCellFormula());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}

}
}
